package com.biz.hunter.dao;

import com.biz.hunter.db.entity.Hunter;
import com.biz.hunter.dbutil.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Repository;
import org.springframework.util.StringUtils;

import java.util.List;

@Repository
public class HunterDAO {

    static Logger logger = LoggerFactory.getLogger(HunterDAO.class);

    protected static final String SQL_SAVE = "insert into tb_hunter(openid,name,englishname,idnumber, age,cityId, workage," + "phone,advantage,email,binded,"
            + "majordomain1, majordomain2, majordomain3,active,createDate,companyId, companyname, comment, "
            + "headimgurl,secureCode,bestDomain) "
            + "values(?,?,?,?" + ",?,?,?,?" + ",?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    protected static final String SQL_SAVE_SECURECODE = "insert into tb_hunter(openid,"
            + "phone, secureCode,expiredTime,state,binded,active ) "
            + "values(?,?,?,?,?,?,?)";
    protected static final String SQL_UPDATE_PHONE = "update tb_hunter set openid=?, "
            + "phone=?,state=? where id =?";

    private static final String SQL_DELETE = "delete from tb_hunter where id=?";

    private static final String SQL_GET_ID = "select * from tb_hunter where id = ? ";

    private static final String SQL_GET_OPENID = "select * from tb_hunter where openid = ? ";
    private static final String SQL_GET_OPENID_STATUS = "select * from tb_hunter where openid = ? and active =?";
    private static final String SQL_GET_ALL = "select * from tb_hunter";

    private static final String SQL_GET_COMPANY = "select * from  tb_hunter " + " where binded = 1 and companyId = ?";

    private static final String SQL_GET_COMPANY_COUNT = "select COUNT(*) from  tb_hunter " + " where binded = 1 and companyId = ?";

    protected static final String SQL_GET_BY_PHONE_OPENID = "select * from tb_hunter  where phone = ? and openId = ?";
    private static final String SQL_GET_HUNTER_COUNT = "SELECT COUNT(*) FROM tb_hunter where 1=1 ";

    private static final String SQL_GET_HUNTER_WITH_LIMIT = "SELECT a.* FROM tb_hunter a  ";

    public int save(Hunter hunter) {
        if (hunter == null) {
            return -1;
        }
        Long id = BaseDAO.save(SQL_SAVE, hunter.getOpenid(), hunter.getName(), hunter.getEnglishname(), hunter.getIdnumber(), hunter.getAge(), hunter.getCityId(), hunter.getWorkage(),
                hunter.getPhone(), hunter.getAdvantage(), hunter.getEmail(), hunter.getBinded(), hunter.getMajordomain1(), hunter.getMajordomain2(),
                hunter.getMajordomain3(), hunter.getActive(), hunter.getCreateDate(), hunter.getCompanyId(), hunter.getCompanyName(), hunter.getComment(),
                hunter.getHeadImgUrl(), hunter.getSecureCode(), hunter.getBestdomain());
        if (id == null) {
            return -1;
        }

        return id.intValue();
    }

    public int saveSecureCode(Hunter hunter) {
        if (hunter == null) {
            return -1;
        }
        Long id = BaseDAO.save(SQL_SAVE_SECURECODE, hunter.getOpenid(), hunter.getPhone(),
                hunter.getSecureCode(), hunter.getExpiredTime(),
                hunter.getState().toString(), 0, 1);
        if (id == null) {
            return -1;
        }

        return id.intValue();
    }

    public int delete(String Id) {

        if (Id != null) {

            int result = BaseDAO.execute(SQL_DELETE, Id);
            return result;
        } else {
            return 0;
        }
    }

    public List<Hunter> getALL() {
        return BaseDAO.queryForList(Hunter.class, SQL_GET_ALL);
    }

    protected static final String SQL_UPDATE = "update tb_hunter "
            + "set openid=?, name=?, englishname=?, "
            + "idnumber =? , cityId=?,  workage=?,"
            + " advantage =?, email=?, "
            + " companyId=?, companyname=?, "
            + " majordomain1=?, majordomain2=?, majordomain3=? ,"
            + " active=?,  comment=?, headImgUrl =?  where id =?";

    public int update(Hunter hunter) {
        return BaseDAO.execute(SQL_UPDATE, hunter.getOpenid(), hunter.getName(), hunter.getEnglishname(),
                hunter.getIdnumber(), hunter.getCityId(), hunter.getWorkage(),
                hunter.getAdvantage(), hunter.getEmail(),
                hunter.getCompanyId(), hunter.getCompanyName(),
                hunter.getMajordomain1(), hunter.getMajordomain2(), hunter.getMajordomain3(),
                hunter.getActive(),
                hunter.getComment(), hunter.getImageUrl(), hunter.getId());
    }

    protected static final String SQL_UPDATE_PROFILE = "update tb_hunter " + " set openid=?, name=?, englishname=?, idnumber=?, "
            + " cityId=?, cityName=?, workage=?, advantage=?, "
            + "email=?, majordomain1=?, domainName1=? ,   majordomain2=? , "
            + "domainName2=?,  majordomain3=? , domainName3=?  , createDate=?,  "
            + "state=?, comment=?, headImgUrl =? , "
            + " bestDomain=? ,serviceTags=?,roleIds=?,binded=? where id =?";

    public int updateProfile(Hunter hunter) {
        return BaseDAO.execute(SQL_UPDATE_PROFILE, hunter.getOpenid(), hunter.getName(), hunter.getEnglishname(), hunter.getIdnumber(), hunter.getCityId(), hunter.getCityName(),
                hunter.getWorkage(), hunter.getAdvantage(),

                hunter.getEmail(), hunter.getMajordomain1(), hunter.getDomainName1(), hunter.getMajordomain2(), hunter.getDomainName2(), hunter.getMajordomain3(),
                hunter.getDomainName3(), hunter.getCreateDate(), hunter.getState(), hunter.getComment(), hunter.getHeadImgUrl(),
                hunter.getBestdomain(), hunter.getServiceTags(), hunter.getRoleIds(), hunter.getBinded(), hunter.getId());
    }

    public int bindedPhone(Hunter hunter) {
        return BaseDAO.execute(SQL_UPDATE_PHONE, hunter.getOpenid(), hunter.getPhone(),
                hunter.getState().toString(), hunter.getId());
    }

    public Hunter getById(Integer id) {
        return BaseDAO.queryForObject(Hunter.class, SQL_GET_ID, id);
    }

    public Hunter getByActivateHunter(String openid) {
        return getByOpenId(openid, Hunter.activate);
    }

    public Hunter getByOpenId(String openid, int status) {

        return BaseDAO.queryForObject(Hunter.class, SQL_GET_OPENID_STATUS, openid, status);
    }

    public Hunter getByOpenId(String openid) {

        return BaseDAO.queryForObject(Hunter.class, SQL_GET_OPENID, openid);
    }

    public List<Hunter> getBindedByCompanyId(int companyId) {
        return BaseDAO.queryForList(Hunter.class, SQL_GET_COMPANY, companyId);
    }

    public Long getBindedByCompanyIdCount(int companyId) {
        return (Long) BaseDAO.queryOneRowOneCol(SQL_GET_COMPANY_COUNT, companyId);
    }


    protected static final String SQL_GET_BY_PHONE = "select * from tb_hunter  where phone = ? ";

    public Hunter getByPhoneandOpenId(String phone, String openId) {
        return BaseDAO.queryForObject(Hunter.class, SQL_GET_BY_PHONE_OPENID, phone, openId);
    }

    public Hunter getByPhone(String phone) {
        return BaseDAO.queryForObject(Hunter.class, SQL_GET_BY_PHONE, phone);
    }

    // public Long getCountOfAll() {
    // return (Long) BaseDAO.queryOneRowOneCol(SQL_GET_HUNTER_COUNT);
    // }
    //
    // public List<Hunter> getAllWithLimit(long start, long length) {
    // return BaseDAO.queryForList(Hunter.class, SQL_GET_HUNTER_WITH_LIMIT,
    // start, length);
    // }
    public Long getCountOfAll(String name, Integer companyId, String phoneNum) {
        String sql = SQL_GET_HUNTER_COUNT;
        if (StringUtils.hasText(name)) {
            sql += " and name like '" + name + "'";
        }
        if (companyId != null) {
            sql += " and companyId =" + companyId + " ";
        }
        if (StringUtils.hasText(phoneNum)) {
            sql += " and phone = '" + phoneNum + "' ";
        }
        return (Long) BaseDAO.queryOneRowOneCol(sql);
    }

    public List<Hunter> getAllWithLimit(String name, Integer companyId, String phoneNum, long start, long length) {
        String sql = SQL_GET_HUNTER_WITH_LIMIT;
        if (StringUtils.hasText(name)) {
            sql += " and a.name like '" + name + "'";
        }
        if (companyId != null) {
            sql += " and companyId =" + companyId + " ";
        }
        if (StringUtils.hasText(phoneNum)) {
            sql += " and a.phone = '" + phoneNum + "' ";
        }
        sql += " LIMIT ?, ?";
        return BaseDAO.queryForList(Hunter.class, sql, start, length);
    }

    private static final String SQL_GET_BY_BESTDOMAIN_PART1 = "select * from  tb_hunter  "
            + " where binded = 1 ";

    private static final String SQL_GET_BY_BESTDOMAIN = "select * from  tb_hunter "
            + " where binded = 1 and (majordomain1 in (?,?,?) or majordomain2 in (?,?,?)"
            + "or  majordomain3 in(?,?,?)) LIMIT ?, ? ";


    public List<Hunter> getbyBestDomain(int offset, int... domainIds) {
        StringBuffer buffer = new StringBuffer();
        String str = "";
        if (domainIds != null && domainIds.length > 0) {
            buffer.append("( ");
            for (int i : domainIds) {
                buffer.append(i).append(",");

            }
            str = buffer.substring(0, buffer.length() - 1);
            str += " )";
        }
        String sql = SQL_GET_BY_BESTDOMAIN_PART1 + "and (majordomain1 in " + str + " or majordomain2 in "
                + str + " or majordomain3 in " + str + " ) ";
        sql += " LIMIT ?, ?";
        logger.debug("get hunter by domain sql is " + SQL_GET_BY_BESTDOMAIN_PART1);


        return BaseDAO.queryForList(Hunter.class, sql, offset, 10);
    }

    public Hunter getByEmail(String emailAddr, String openId) {
        String sql = "select * from tb_hunter where email like ? and openId like ?";
        return BaseDAO.queryForObject(Hunter.class, sql, emailAddr, openId);

    }

    public int deactivate(Hunter hunter) {
        return BaseDAO.execute("update tb_hunter set active =? where openid =? ",
                hunter.getActive(), hunter.getOpenid()
        );
    }

    public void activate(Hunter hunter) {
        BaseDAO.execute("update tb_hunter set active =? where openid =? ",
                hunter.getActive(), hunter.getOpenid()
        );
    }

    public void updateImage(Hunter hunter) {
        BaseDAO.execute("update tb_hunter set imageUrl =? where openid =? ",
                hunter.getImageUrl(), hunter.getOpenid()
        );
    }


    public void approve(int id) {
        BaseDAO.execute("update tb_hunter set binded = '1' where id = ? ", id);

    }
}
